﻿using Common;
using Models;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Data;
using System.Linq;
using System.Text;
using Dapper;

namespace DbControl
{
    public class OprOil
    {
        public static List<oils> GetOilAll()
        {
            try
            {
                using (IDbConnection connection = new SqlConnection(OprSql.connectString))
                {
                    string sql = @"SELECT id 
                          ,oil_code 
                          ,oil_name 
                          ,oil_mode 
                          ,oil_type 
                          ,oil_change_interval 
                          ,oil_fill_time 
                          ,oil_indexA
                          ,oil_indexB
                          ,flag 
                      FROM oils WHERE flag>0 ";
                    return connection.Query<oils>(sql).ToList();
                }
            }
            catch (Exception ex)
            {
                lLogWrite.WriteLog(ex.ToString());
                return null;
            }
        }
        public static DataTable GetOilByName(string Name)
        {
            using (IDbConnection connection = new SqlConnection(OprSql.connectString))
            {
                string sql = "";
                if (string.IsNullOrEmpty(Name))
                {
                    sql = @"SELECT id 
                          ,oil_code 
                          ,oil_name 
                          ,oil_mode 
                          ,oil_type 
                          ,oil_change_interval 
                          ,oil_fill_time 
                          ,oil_indexA
                          ,oil_indexB
                          ,flag 
                      FROM  oils ";
                }
                else
                {
                    sql = string.Format(@"SELECT id 
                            ,oil_code 
                            ,oil_name 
                            ,oil_mode 
                            ,oil_type 
                            ,oil_change_interval 
                            ,oil_fill_time 
                            ,oil_indexA
                            ,oil_indexB
                            ,flag 
                        FROM  oils   WHERE  oil_name='{0}'", Name);
                }
                DataTable dt = new DataTable();
                dt.Load(connection.ExecuteReader(sql));
                return dt;
            }
        }
        public static oils GetOilById(int id)
        {
            using (IDbConnection connection = new SqlConnection(OprSql.connectString))
            {
                string sql = @"SELECT id 
                          ,oil_code 
                          ,oil_name 
                          ,oil_mode 
                          ,oil_type 
                          ,oil_change_interval 
                          ,oil_fill_time 
                          ,oil_indexA
                          ,oil_indexB
                          ,flag 
                      FROM oils  WHERE   id=@id";
                return connection.Query<oils>(sql, new { id = id }).SingleOrDefault();
            }
        }
        public static bool IsExistOilCode(string oil_code)
        {
            int intNum = 0;
            try
            {
                using (IDbConnection connection = new SqlConnection(OprSql.connectString))
                {
                    string sql = @"select COUNT(id)  from oils WHERE  oil_code =@oil_code ";
                    intNum = (int)connection.ExecuteScalar(sql, new { oil_code = oil_code });
                }
            }
            catch (Exception ex)
            {
                lLogWrite.WriteLog(ex.ToString());
            }
            if (intNum > 0)
            {
                return true;
            }

            return false;
        }
        public static bool IsExistOilName(string oil_name)
        {
            int intNum = 0;
            try
            {
                using (IDbConnection connection = new SqlConnection(OprSql.connectString))
                {
                    string sql = @"select COUNT(id)  from oils WHERE  oil_name =@oil_name ";
                    intNum = (int)connection.ExecuteScalar(sql, new { oil_name = oil_name });
                }
            }
            catch (Exception ex)
            {
                lLogWrite.WriteLog(ex.ToString());
            }
            if (intNum > 0)
            {
                return true;
            }

            return false;
        }       
        public static int DeleteById(int id)
        {
            try
            {
                using (IDbConnection connection = new SqlConnection(OprSql.connectString))
                {
                    string sql = @"DELETE FROM oils WHERE id=@id";
                    sql = @"UPDATE oils SET flag = 0 WHERE id=@id";
                    return connection.Execute(sql, new { id = id });
                }
            }
            catch (Exception ex)
            {
                lLogWrite.WriteLog(ex.ToString());
                return 0;
            }
        }
        public static int Update(oils dv)
        {
            try
            {
                using (IDbConnection connection = new SqlConnection(OprSql.connectString))
                {
                    string sql = @"UPDATE  oils
                                   SET oil_code= @oil_code
                                      ,oil_name= @oil_name 
                                      ,oil_mode= @oil_mode 
                                      ,oil_type= @oil_type 
                                      ,oil_change_interval= @oil_change_interval
                                      ,oil_fill_time= @oil_fill_time
                                      ,oil_indexA=@oil_indexA
                                      ,oil_indexB=@oil_indexB
                                      ,flag= @flag                                      
                                 WHERE id=@id";
                    return connection.Execute(sql, dv);
                }
            }
            catch (Exception ex)
            {
                lLogWrite.WriteLog(ex.ToString());
                return 0;
            }
        }
        public static int Insert(oils dv)
        {
            try
            {
                using (IDbConnection connection = new SqlConnection(OprSql.connectString))
                {
                    string sql = @"INSERT INTO oils
                                   (oil_code 
                                   ,oil_name 
                                   ,oil_mode 
                                   ,oil_type 
                                   ,oil_change_interval 
                                   ,oil_fill_time 
                                   ,oil_indexA
                                   ,oil_indexB
                                   ,flag)
                             VALUES
                                   (@oil_code 
                                   ,@oil_name 
                                   ,@oil_mode 
                                   ,@oil_type 
                                   ,@oil_change_interval
                                   ,@oil_fill_time
                                   ,@oil_indexA
                                   ,@oil_indexB
                                   ,@flag)";
                    return connection.Execute(sql, dv);
                }
            }
            catch (Exception ex)
            {
                lLogWrite.WriteLog(ex.ToString());
                return 0;
            }
        }
    }
}
